package com.dao;


import com.domain.UserActPlanActivity;
import com.utils.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class UserActPlanActivityDao {
    //创建jdbcTemplate对象
    private JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

    /**
     * 根据用户id查询所有
     * @param userId 传入Integer用户的id
     * @return 返回一个UserActPlanActivity类型的对象
     */
    public List<UserActPlanActivity> FindUAP(Integer userId) {
        String sql = "SELECT a.userid,u.username,u.phone,u.password,u.address,u.grader,a.activityid ,a.activityname ,a.activitydate ,a.activitycontent,a.activitytype ,a.activitygrade,a.signin,a.result,a.feedback,a.degree FROM `user` u," +
                "(SELECT act.userid,act.activityid ,a.activityname ,a.activitydate ,a.activitycontent,a.activitytype ,a.activitygrade,act.signin,act.result,act.feedback,act.degree FROM activity a," +
                "(SELECT  act.userid,act.activityid ,act.signin,act.result,act.feedback,act.degree FROM actplan act " +
                "WHERE act.userid=?) act WHERE a.activityid=act.activityid) a " +
                "WHERE u.userid=a.userid";

        List<UserActPlanActivity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UserActPlanActivity.class), userId);
        return list;
    }

    /**
     * 根据活动id查询所有
     * @param activityId 传入Integer活动的id
     * @return 返回一个UserActPlanActivity类型的对象
     */
    public List<UserActPlanActivity> FindActUser(Integer activityId) {
        String sql = "SELECT a.userid,u.username,u.phone,u.password,u.address,u.grader,a.activityid ,a.activityname ,a.activitydate ,a.activitycontent,a.activitytype ,a.activitygrade,a.signin,a.result,a.feedback,a.degree FROM `user` u," +
                "(SELECT act.userid,act.activityid ,a.activityname ,a.activitydate ,a.activitycontent,a.activitytype ,a.activitygrade,act.signin,act.result,act.feedback,act.degree FROM activity a," +
                "(SELECT  act.userid,act.activityid ,act.signin,act.result,act.feedback,act.degree FROM actplan act " +
                "WHERE act.activityId=?) act WHERE a.activityid=act.activityid) a " +
                "WHERE u.userid=a.userid";

        List<UserActPlanActivity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UserActPlanActivity.class), activityId);
        return list;
    }

}
